package com.zyd.dao;

import com.zyd.io.model.VehicleEntity;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * 小程序用车辆dao
 */
@Repository
public class VehicleDao {

    private static final Logger LOGGER = LoggerFactory.getLogger(VehicleDao.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 锁车 解锁
     *
     * @param tagcode
     * @param state
     */
    public void updateLockByTagcode(String tagcode, String state) {
        jdbcTemplate.update("UPDATE zydzn_vehicle AS v SET v.islock= ? WHERE v.tagcode = ? ", state, tagcode);
    }

    /**
     * 根据标签id在轨迹表查询最新的3条基站id
     *
     * @param tagid
     * @return
     */
    public List<String> findBaseidByTagidFromSocketNowTOP3(String tagid) {
        String sql = "SELECT base_id FROM socket_now WHERE tag_id= ?  GROUP BY base_id ORDER BY MAX(rec_time) DESC LIMIT 3 ";
        return jdbcTemplate.queryForList(sql, String.class, tagid);
    }

    /**
     * 查询锁车车辆的tagcode
     *
     * @param i
     * @return
     */
    public List<String> findTagcodeByLock(Integer i) {
        return jdbcTemplate.queryForList("SELECT tagcode FROM zydzn_vehicle WHERE dr = 0 AND `islock` = ? ", String.class, i);

    }

    /**
     * 查询出该车辆最新的基站记录
     *
     * @param tagcode
     * @return
     */
    public String findNewBaseidByTagcode(String tagcode) {
        String baseid = null;
        try {
            baseid = jdbcTemplate.queryForObject("SELECT base_id FROM socket_now WHERE tag_id=? ORDER BY rec_time DESC LIMIT 1 ", String.class, tagcode);
        } catch (DataAccessException e) {
            LOGGER.info("根据tagcode:" + tagcode + "未查询到最新的基站数据");
        }
        return baseid;
    }

    /**
     * 根据tagcode查询他的手机号,
     *
     * @param tagcode
     * @return
     */
    public String findPhoneByTagcode(String tagcode) {
        String mobile = null;
        try {
            mobile = jdbcTemplate.queryForObject("SELECT mobile FROM zydzn_vehicle WHERE tagcode= ? LIMIT 1 ", String.class, tagcode);
        } catch (DataAccessException e) {
            LOGGER.info("根据tagcode:" + tagcode + "未查询到手机号");

        }
        return mobile;
    }

    /**
     * 根据tagcode查询车辆是否锁定
     *
     * @param tagcode
     */
    public Integer findIslockByTagcode(String tagcode) {
        Integer islock = null;
        try {
            islock = jdbcTemplate.queryForObject("SELECT islock FROM zydzn_vehicle WHERE dr=0 AND tagcode= ? LIMIT 1 ", Integer.class, tagcode);
        } catch (DataAccessException e) {
            LOGGER.info("根据tagcode:" + tagcode + "未查询到车辆是否锁定信息");


        }
        return islock;
    }

    /**
     * vehicleId 查询对应的车辆的最新的保险信息
     *
     * @param vehicleId
     * @return
     */
    public Map findMealByVehicleId(String vehicleId) {
        Map<String, Object> map = null;
        try {
            map = jdbcTemplate.queryForMap("SELECT m.id AS mealId ,m.start_time AS startTime,m.end_time AS endTime ,s.salename ,s.salemny AS money,s.id AS safeId FROM `zydzn_meal` AS m LEFT JOIN zydzn_safe AS s ON m.meal_id=s.id WHERE m.vehicle_id= ? ORDER BY m.end_time DESC LIMIT 1", vehicleId);
        } catch (DataAccessException e) {
            LOGGER.info("根据vehicleId:" + vehicleId + "未查询到对应的保险数据");
        }
        return map;
    }

    /**
     * 向车辆审核表(过渡表)插入一条数据
     *
     * @param v  默认状态为 0
     */
    public void addVehicleToExamine(VehicleEntity v) {
        addVehicleToExamine(v,0);
    }
    /**
     * 向车辆审核表(过渡表)插入一条数据
     *
     * @param v
     * @param state 状态 0为审核中,1为审核通过,2为微信未支付,3为微信支付成功
     */
    public void addVehicleToExamine(VehicleEntity v, Integer state) {
        String sql = "INSERT INTO zydzn_vehicle_examine ( id, carname, salesite,carid, sex, mobile, vehiclebrand, mobileback, caraddress, vinno, carcolour, tagcode, keeptime, bugtime, regtime, img1, img2, img3, createtime, userid, cartype, newareaid, meal_id, meal_money, meal_name, state) VALUES (?, ?, ? , ? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
        jdbcTemplate.update(sql, v.getId(), v.getCarname(),v.getSalesite(), v.getCarid(), v.getSex(), v.getMobile(), v.getVehiclebrand(), v.getMobileback(), v.getCaraddress(), v.getVinno(), v.getCarcolour(), v.getTagcode(), v.getKeeptime(), v.getBugtime(), v.getRegtime(), v.getImg1(), v.getImg2(), v.getImg3(), v.getCreatetime(), v.getUserid(), v.getCartype(), v.getNewareaid(), v.getMeal_id(), v.getMeal_money(), v.getMeal_name(), state);
    }

    /**
     * 通过车辆id 从车辆审核表(过渡表)查询一条数据
     *
     * @param id
     * @return
     */
    public VehicleEntity findVehicleByIdFromExamine(String id) {
        String sql = "SELECT `id`,`carname`,`salesite`,`carid`,`sex`,`mobile`,`vehiclebrand`,`mobileback`,`caraddress`,`vinno`,`carcolour`,`tagcode`,`keeptime`,`bugtime`,`regtime`,`img1`,`img2`,`img3`,`createtime`,`userid`,`cartype`,`newareaid`,`meal_id`,`meal_money`,`meal_name` FROM `zydzn_vehicle_examine` WHERE state IN (0,2) AND `id`= ? ";
        VehicleEntity result = null;
        try {
            result = jdbcTemplate.queryForObject(sql, new RowMapper<VehicleEntity>() {
                @Override
                public VehicleEntity mapRow(ResultSet resultSet, int i) throws SQLException {
                    VehicleEntity vehicle = new VehicleEntity();
                    vehicle.setId(resultSet.getString("id"));
                    vehicle.setCarname(resultSet.getString("carname"));
                    vehicle.setSalesite(resultSet.getString("salesite"));
                    vehicle.setCarid(resultSet.getString("carid"));
                    vehicle.setSex(resultSet.getInt("sex"));
                    vehicle.setMobile(resultSet.getString("mobile"));
                    vehicle.setVehiclebrand(resultSet.getString("vehiclebrand"));
                    vehicle.setMobileback(resultSet.getString("mobileback"));
                    vehicle.setCaraddress(resultSet.getString("caraddress"));
                    vehicle.setVinno(resultSet.getString("vinno"));
                    vehicle.setCarcolour(resultSet.getString("carcolour"));
                    vehicle.setTagcode(resultSet.getString("tagcode"));
                    vehicle.setKeeptime(resultSet.getString("keeptime"));
                    vehicle.setBugtime(resultSet.getString("bugtime"));
                    vehicle.setRegtime(resultSet.getString("regtime"));
                    vehicle.setImg1(resultSet.getString("img1"));
                    vehicle.setImg2(resultSet.getString("img2"));
                    vehicle.setImg3(resultSet.getString("img3"));
                    vehicle.setCreatetime(resultSet.getString("createtime"));
                    vehicle.setUserid(resultSet.getString("userid"));
                    vehicle.setCartype(resultSet.getString("cartype"));
                    vehicle.setNewareaid(resultSet.getString("newareaid"));
                    vehicle.setMeal_id(resultSet.getString("meal_id"));
                    vehicle.setMeal_money(resultSet.getBigDecimal("meal_money"));
                    vehicle.setMeal_name(resultSet.getString("meal_name"));
                    return vehicle;
                }
            },id);
            } catch (DataAccessException e) {
            LOGGER.error("通过车辆id:" + id + ",未在zydzn_vehicle_examine表中查询到状态为0(未审核的)数据");
        }
        return result;
    }

    /**
     * 修改 车辆审核表(过渡表)的状态值
     * @param vehicleId 车id
     * @param state 状态  0 为未审核默认,1为通过,2 为不通过
     */
    public void updateExamineVehicleState(String vehicleId, Integer state) {
        String sql ="UPDATE `zydzn_vehicle_examine` SET `state`  =  ?  WHERE `id`= ? ;";
        jdbcTemplate.update(sql,state,vehicleId );
    }

    /**
     * 通过userid 跟 手机号 去zydzn_vehicle_examine表查询不为1(审核通过的)数据
     * @param userid
     * @param mobile
     * @return
     */
    public List<VehicleEntity> findVehicleExamineByUseridOrMobile(String userid, String mobile) {
        String sql ="SELECT v.id, v.carname,v.salesite, v.carid, v.sex, v.mobile, v.vehiclebrand, v.mobileback, v.caraddress, v.vinno, v.carcolour, v.tagcode, v.keeptime, v.bugtime, v.regtime, v.img1, v.img2, v.img3, v.createtime, v.userid, v.cartype, v.newareaid, v.meal_id, v.meal_money, v.meal_name, c.name AS area FROM zydzn_vehicle_examine AS v LEFT JOIN zydzn_country AS c ON v.newareaid=c.id WHERE v.state = 0  AND ( v.userid= ? OR v.mobile= ? ) ";
        return jdbcTemplate.query(sql, new RowMapper<VehicleEntity>() {
            @Override
            public VehicleEntity mapRow(ResultSet resultSet, int i) throws SQLException {
                VehicleEntity vehicle = new VehicleEntity();
                vehicle.setId(resultSet.getString("id"));
                vehicle.setCarname(resultSet.getString("carname"));
                vehicle.setSalesite(resultSet.getString("salesite"));
                vehicle.setCarid(resultSet.getString("carid"));
                vehicle.setSex(resultSet.getInt("sex"));
                vehicle.setMobile(resultSet.getString("mobile"));
                vehicle.setVehiclebrand(resultSet.getString("vehiclebrand"));
                vehicle.setMobileback(resultSet.getString("mobileback"));
                vehicle.setCaraddress(resultSet.getString("caraddress"));
                vehicle.setVinno(resultSet.getString("vinno"));
                vehicle.setCarcolour(resultSet.getString("carcolour"));
                vehicle.setTagcode(resultSet.getString("tagcode"));
                vehicle.setKeeptime(resultSet.getString("keeptime"));
                vehicle.setBugtime(resultSet.getString("bugtime"));
                vehicle.setRegtime(resultSet.getString("regtime"));
                vehicle.setImg1(resultSet.getString("img1"));
                vehicle.setImg2(resultSet.getString("img2"));
                vehicle.setImg3(resultSet.getString("img3"));
                vehicle.setCreatetime(resultSet.getString("createtime"));
                vehicle.setUserid(resultSet.getString("userid"));
                vehicle.setCartype(resultSet.getString("cartype"));
                vehicle.setNewareaid(resultSet.getString("newareaid"));
                vehicle.setMeal_id(resultSet.getString("meal_id"));
                vehicle.setMeal_money(resultSet.getBigDecimal("meal_money"));
                vehicle.setMeal_name(resultSet.getString("meal_name"));
                vehicle.setArea(resultSet.getString("area"));
                return vehicle;
            }
        },userid,mobile);
    }
}
